﻿CREATE OR REPLACE FUNCTION trgfnwhen_insert_update_delete_pol_updatepo()
  RETURNS trigger AS
	$BODY$
		DECLARE
			using_id integer:=0;
		Begin
			IF TG_OP='INSERT' THEN
				using_id = new.order_id;
			ELSEIF TG_OP='UPDATE' THEN
				IF COALESCE(OLD.plan_qty,0)<>COALESCE(NEW.plan_qty,0) OR COALESCE(OLD.PRODUCT_QTY,0)<>COALESCE(NEW.PRODUCT_QTY,0) OR COALESCE(OLD.PRICE_UNIT,0)<>COALESCE(NEW.PRICE_UNIT,0) THEN
					using_id = OLD.order_id;
				END IF;
			ELSE
				using_id = OLD.order_id;
			end if;
			IF USING_ID>0 THEN
				Update purchase_order 
				set 
					amount_untaxed=(Select  
							sum(price_unit*case when state<>'done' and po.special_case=True then plan_qty else product_qty end) as price_subtotal
							from
							    purchase_order po
							left join
							    purchase_order_line pol on po.id = pol.order_id
							where po.id=using_id group by po.id),
					tax_amount=(Select 
							    sum(price_subtotal2)*(coalesce(tax_per,0)/100.0) as tax_amount
							from 
							    purchase_order_line pol
							right join
							    purchase_order po on pol.order_id = po.id where po.id=using_id group by po.id,tax_per)
				where id=using_id;					
			END IF;
			Return NULL;
		end;
	$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
drop TRIGGER trgdupdate_insert_pol_updatepo_amount on purchase_order_line;
CREATE TRIGGER trgdupdate_insert_pol_updatepo_amount
AFTER Update or Insert or delete
ON purchase_order_line
FOR EACH ROW
EXECUTE PROCEDURE trgfnwhen_insert_update_delete_pol_updatepo();